package com.openlogic.logic.service.datasource.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.noear.solon.annotation.Component;
import org.noear.solon.annotation.Inject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.openlogic.api.logic.dto.RuleSqlModelColumnDto;
import com.openlogic.api.logic.entity.RuleModel;
import com.openlogic.api.logic.entity.RuleSqlModelColumn;
import com.openlogic.common.core.exception.logic.RuleException;
import com.openlogic.common.core.utils.DateUtils;
import com.openlogic.common.logic.utils.StackExceptionUtils;
import com.openlogic.common.plugins.db.api.utils.DataSourceUtils;
import com.openlogic.common.plugins.spi.enums.DbType;
import com.openlogic.datasource.api.dto.SqlConsoleDto;
import com.openlogic.datasource.api.dto.SqlConsoleDto.InParameter;
import com.openlogic.datasource.api.entity.DataSourceEntity;
import com.openlogic.datasource.api.enums.SqlParamType;
import com.openlogic.datasource.api.param.ConnectionParam;
import com.openlogic.datasource.api.vo.SqlConsoleVo;
import com.openlogic.logic.concurent.CallableTemplate;
import com.openlogic.logic.concurent.DataTableDetailHander;
import com.openlogic.logic.concurent.DateHander;
import com.openlogic.logic.concurent.RuleSqlColumnHander;
import com.openlogic.logic.service.datasource.DataSourceService;
import com.openlogic.logic.service.datasource.SqlConsoleService;

import cn.hutool.core.collection.CollUtil;
import jodd.db.connection.ConnectionProvider;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;

/**
* @ClassName: SqlConsoleServiceImpl
* @Description: SQL控制台Service实现层
* @author weiyongpeng
* @date 2024年11月24日 上午10:16:52
 */
@Component
public class SqlConsoleServiceImpl implements SqlConsoleService {
	
	private static final Logger log = LoggerFactory.getLogger(SqlConsoleServiceImpl.class);
	
    private static Map<String, List<Connection>> connectionMap = new ConcurrentHashMap<>();
    
    private static Map<String, List<ConnectionProvider>> templateMap = new ConcurrentHashMap<>();
    
    @Inject
    private DataSourceService dataSourceService;

	@Override
	public List<SqlConsoleVo> sqlRun(SqlConsoleDto sqlConsoleDto) {
		String sqlKey = sqlConsoleDto.getSqlKey();
		List<InParameter> params = sqlConsoleDto.getParam(); // 条件参数集
		Integer maxCount = sqlConsoleDto.getMaxCount(); // 页大小
		String parseSql = sqlConsoleDto.getSqlText();
		Statements stmts;
		try {
			if (CollectionUtils.isNotEmpty(params) && StringUtils.isNotBlank(parseSql)) {
				parseSql = params.stream().reduce(parseSql, (currentSql, param) -> {
					String placeholder = "${" + param.getKey() + "}";
					convertValue(param);
					return currentSql.replace(placeholder, param.getValue().toString());
				}, (sql1, sql2) -> sql1); // 合并阶段，省略这里的合并逻辑
				log.info("parseSql: {}", parseSql);
			}
            stmts = CCJSqlParserUtil.parseStatements(parseSql);
        } catch (JSQLParserException e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("SQL语法有问题，解析出错");
        }
		List<Statement> sqls = stmts.getStatements();
        if (CollUtil.isEmpty(sqls)) {
            throw new RuleException("未解析到SQL语句");
        }
        DataSourceEntity dataSource = dataSourceService.getDataSourceById(sqlConsoleDto.getSourceId());
        if(dataSource == null){
            throw new RuleException("SQL工作台查询数据源出错");
        }
        DbType dbType = DbType.getDbType(dataSource.getDbType());
        ConnectionParam connectionParam = dataSource.getConnectionParams();
        
        // 定义计数器
        final CountDownLatch latch = new CountDownLatch(sqls.size());
        // 定义固定长度的线程池
        ExecutorService executorService = Executors.newFixedThreadPool(sqls.size());
        // Callable用于产生结果
        List<CallableTemplate<SqlConsoleVo>> tasks = new ArrayList<>();
        List<Connection> conns = new ArrayList<>();
        sqls.stream().forEach(sql -> {
        	Connection connection = DataSourceUtils.getConnection(dbType, connectionParam);
        	List<Map<String,String>> columnMeta = DataSourceUtils.getTableColumnMeta(dbType, connectionParam, sqlConsoleDto.getTableName());
        	conns.add(connection);
            DateHander dateHander = new DateHander(latch, connection, sql.toString(), maxCount, columnMeta);
            tasks.add(dateHander);
        });
        connectionMap.put(sqlKey, conns);
        // Future用于获取结果
        List<SqlConsoleVo> result = new ArrayList<>();
        List<Future<SqlConsoleVo>> futures;
        try {
            futures = executorService.invokeAll(tasks);
            // 主线程阻塞，等待所有子线程执行完成
            latch.await(60,TimeUnit.SECONDS);
            // 处理线程返回结果
            for (Future<SqlConsoleVo> future : futures) {
                result.add(future.get());
            }
        } catch (Exception e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("查询异常:" +  e.getMessage());
        }
        // 关闭线程池
        executorService.shutdown();
        // 执行完清除
        connectionMap.remove(sqlKey);
        return result;
	}
	
	@Override
	public List<SqlConsoleVo> execSql(SqlConsoleDto sqlConsoleDto) {
		
		String sqlKey = sqlConsoleDto.getSqlKey();
		List<InParameter> params = sqlConsoleDto.getParam(); // 条件参数集
		String parseSql = sqlConsoleDto.getSqlText();
		Statements stmts;
		try {
			if (CollectionUtils.isNotEmpty(params) && StringUtils.isNotBlank(parseSql)) {
				parseSql = params.stream().reduce(parseSql, (currentSql, param) -> {
					String placeholder = "${" + param.getKey() + "}";
					convertValue(param);
					return currentSql.replace(placeholder, param.getValue().toString());
				}, (sql1, sql2) -> sql1); // 合并阶段，省略这里的合并逻辑
				log.info("parseSql: {}", parseSql);
			}
            stmts = CCJSqlParserUtil.parseStatements(parseSql);
        } catch (JSQLParserException e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("SQL语法有问题，解析出错");
        }
		List<Statement> sqls = stmts.getStatements();
        if (CollUtil.isEmpty(sqls)) {
            throw new RuleException("未解析到SQL语句");
        }
        DataSourceEntity dataSource = dataSourceService.getDataSourceById(sqlConsoleDto.getSourceId());
        if(dataSource == null){
            throw new RuleException("SQL工作台查询数据源出错");
        }
        DbType dbType = DbType.getDbType(dataSource.getDbType());
        ConnectionParam connectionParam = dataSource.getConnectionParams();
        
        // 定义计数器
        final CountDownLatch latch = new CountDownLatch(2);
        // 定义固定长度的线程池
        ExecutorService executorService = Executors.newFixedThreadPool(sqls.size());
        // Callable用于产生结果
        List<CallableTemplate<SqlConsoleVo>> tasks = new ArrayList<>();
        List<ConnectionProvider> conns = new ArrayList<>();
		sqls.stream().forEach(sql -> {
			ConnectionProvider jdbcTemplate = DataSourceUtils.getJdbcTemplate(dbType, connectionParam);
			List<Map<String, String>> columnMeta = DataSourceUtils.getTableColumnMeta(dbType, connectionParam,
					sqlConsoleDto.getTableName());
			String pageSql = DataSourceUtils.generatePageSql(dbType, sql.toString(), sqlConsoleDto.getPage(),
					sqlConsoleDto.getPageSize());
			String countSql = DataSourceUtils.generateCountSql(dbType, sqlConsoleDto.getTableName());
			conns.add(jdbcTemplate);
			DataTableDetailHander dateHander = new DataTableDetailHander(latch, jdbcTemplate, pageSql, countSql,
					columnMeta);
			tasks.add(dateHander);
		});
        templateMap.put(sqlKey, conns);
        // Future用于获取结果
        List<SqlConsoleVo> result = new ArrayList<>();
        List<Future<SqlConsoleVo>> futures;
        try {
            futures = executorService.invokeAll(tasks);
            // 主线程阻塞，等待所有子线程执行完成 等待超过30s则认为超时
            latch.await(60,TimeUnit.SECONDS);
            // 处理线程返回结果
            for (Future<SqlConsoleVo> future : futures) {
                result.add(future.get());
            }
        } catch (Exception e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("查询异常:" +  e.getMessage());
        }
        // 关闭线程池
        executorService.shutdown();
        // 执行完清除
        templateMap.remove(sqlKey);
        return result;
	}
	
	@Override
	public List<RuleSqlModelColumn> parseSql(RuleModel ruleModel) {
		String sqlKey = String.valueOf(new Date().getTime());
		List<InParameter> params = Optional.ofNullable(ruleModel.getParam()).isPresent() ? ruleModel.getParam().stream().map(p -> {
			InParameter inParameter = new InParameter();
			inParameter.setKey(p.getKey());
			inParameter.setLabel(p.getLabel());
			inParameter.setType(p.getType());
			inParameter.setValue(p.getValue());
			return inParameter;
		}).collect(Collectors.toList()) : new ArrayList<>();
		String parseSql = ruleModel.getSqlexp();
		String parseSqlUpper = parseSql.toUpperCase();
		String fromFind = "FROM";
        String whereFind = "WHERE";
 
        int index = parseSqlUpper.indexOf(fromFind);
        int whereIndex = parseSqlUpper.indexOf(whereFind);
        String tableName = parseSqlUpper.substring(index+4, whereIndex == -1 ? parseSqlUpper.length() : whereIndex).trim().replace(";", "");
        
		Statements stmts;
		try {
			if (CollectionUtils.isNotEmpty(params) && StringUtils.isNotBlank(parseSql)) {
				parseSql = params.stream().reduce(parseSql, (currentSql, param) -> {
					String placeholder = "${" + param.getKey() + "}";
					convertValue(param);
					return currentSql.replace(placeholder, param.getValue().toString());
				}, (sql1, sql2) -> sql1); // 合并阶段，省略这里的合并逻辑
				log.info("parseSql: {}", parseSql);
			}
            stmts = CCJSqlParserUtil.parseStatements(parseSql);
        } catch (JSQLParserException e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("SQL语法有问题，解析出错");
        }
		List<Statement> sqls = stmts.getStatements();
        if (CollUtil.isEmpty(sqls)) {
            throw new RuleException("未解析到SQL语句");
        }
        DataSourceEntity dataSource = dataSourceService.getDataSourceById(ruleModel.getSqlDataSourceId());
        if(dataSource == null){
            throw new RuleException("SQL工作台查询数据源出错");
        }
        DbType dbType = DbType.getDbType(dataSource.getDbType());
        ConnectionParam connectionParam = dataSource.getConnectionParams();
        
        // 定义计数器
        final CountDownLatch latch = new CountDownLatch(sqls.size());
        // 定义固定长度的线程池
        ExecutorService executorService = Executors.newFixedThreadPool(sqls.size());
        // Callable用于产生结果
        List<CallableTemplate<RuleSqlModelColumnDto>> tasks = new ArrayList<>();
        List<ConnectionProvider> conns = new ArrayList<>();
        sqls.stream().forEach(sql -> {
        	ConnectionProvider jdbcTemplate = DataSourceUtils.getJdbcTemplate(dbType, connectionParam);
        	List<Map<String,String>> columnMeta = DataSourceUtils.getTableColumnMeta(dbType, connectionParam, tableName);
        	String pageSql = sql.toString();
        	conns.add(jdbcTemplate);
        	RuleSqlColumnHander dateHander = new RuleSqlColumnHander(latch, jdbcTemplate, pageSql, ruleModel.getId(), columnMeta);
            tasks.add(dateHander);
        });
        templateMap.put(sqlKey, conns);
        // Future用于获取结果
        RuleSqlModelColumnDto result = new RuleSqlModelColumnDto();
        List<Future<RuleSqlModelColumnDto>> futures;
        try {
            futures = executorService.invokeAll(tasks);
            // 主线程阻塞，等待所有子线程执行完成
            latch.await(60,TimeUnit.SECONDS);
            // 处理线程返回结果
            for (Future<RuleSqlModelColumnDto> future : futures) {
                result = future.get();
            }
        } catch (Exception e) {
            log.error("全局异常信息ex={}, StackTrace={}", e.getMessage(), StackExceptionUtils.getStackTrace(e));
            throw new RuleException("查询异常:" +  e.getMessage());
        }
        // 关闭线程池
        executorService.shutdown();
        // 执行完清除
        templateMap.remove(sqlKey);
        return result.getRuleSqlModelColumns();
	}

	@Override
	public void sqlStop(SqlConsoleDto sqlConsoleDto) {
		String sqlKey = sqlConsoleDto.getSqlKey();
        List<Connection> conns = connectionMap.get(sqlKey);
        if (CollUtil.isNotEmpty(conns)) {
            for (int i = 0; i < conns.size(); i++) {
                Connection conn = conns.get(i);
                try {
                    if (null != conn && !conn.isClosed()) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    throw new RuleException("SQL工作台停止出错");
                }
            }
        }
	}

	/**
	* 描述：数据值转化
	* @Title: convertValue
	* @param param
	* @author weiyongpeng
	* @date  2024年11月24日 下午1:38:22
	 */
	private void convertValue(InParameter param) {
		switch (SqlParamType.getTypeByValue(param.getType())) {
		case STRING:
			param.setValue("'" + param.getValue() + "'");
			break;
		case INTEGER:
			param.setValue(Integer.parseInt(param.getValue().toString()));
			break;
		case DATE:
			param.setValue(DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD, DateUtils.parseDate(param.getValue().toString(), DateUtils.YYYY_MM_DD)));
			break;
		case DECIMAL:
			param.setValue(new BigDecimal(param.getValue().toString()));
		case BOOLEAN:
			param.setValue(Boolean.parseBoolean(param.getValue().toString()));
			break;
		default:
			param.setValue("'" + param.getValue() + "'");
			break;
		}
	}

}
